package com.demo.dao_admin;

import java.util.List;
import java.util.Optional;

import org.springframework.data.jpa.repository.EntityGraph;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import com.demo.entity.ExamDTO;
import com.demo.entity.ExamDTO2;
import com.demo.entity.ExamExams;

@Repository("adminExamExamsDAO")
public interface ExamExamsDAO extends JpaRepository<ExamExams, Long>, JpaSpecificationExecutor<ExamExams> {

	//解决关联查询的N+1问题
	@EntityGraph(value = "ExamExams.examUsers", type = EntityGraph.EntityGraphType.FETCH)
	Optional<ExamExams> findById(Long id);
	
	//查找某场考试的学员信息及成绩，方式一
	@Query(value = "select new com.demo.entity.ExamDTO(ee,eu,u) "
			+ "from ExamExams ee,ExamUser eu,UserInformation u "
			+ "where ee.id=eu.examId and u.id=eu.userId and ee.id=:id")
	List<ExamDTO> findExamUserByExamId1(Long id);
	
	//查找某场考试的学员信息及成绩,方式二，颗粒度更细，产生的sql条数更少，推荐
	@Query(value = "select new com.demo.entity.ExamDTO2(ee.examsName,eu.userId,eu.cord,u.uname) "
			+ "from ExamExams ee,ExamUser eu,UserInformation u "
			+ "where ee.id=eu.examId and u.id=eu.userId and ee.id=:id")
	List<ExamDTO2> findExamUserByExamId2(Long id);
	
	//查找某场考试的学员信息及成绩,方式三，和方式二差不多，用join代替，还可使用外连接查询
	@Query(value = "select new com.demo.entity.ExamDTO2(ee.examsName,eu.userId,eu.cord,u.uname) "
			+ "from ExamExams ee "
			+ "LEFT JOIN ExamUser eu ON eu.examId=ee.id "
			+ "LEFT JOIN UserInformation u "
			+ "ON eu.userId=u.id where ee.id=:id")
	List<ExamDTO2> findExamUserByExamId3(Long id);
}